import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import openpyxl  # xlsx 处理
import xlrd  # xls 处理（需安装 xlrd==1.2.0）
from openpyxl.utils import column_index_from_string
import os ,sys
import json
from datetime import datetime

class ExcelFilterTool:
    def __init__(self, root):
        self.root = root
        self.root.title("LP软包电池尺寸筛选工具")
        self.root.geometry("800x670")
        self.root.resizable(True, True)
        
        self.font_config()
        self.file_path = ""
        self.history_file = self.get_app_data_path("excel_filter_history.json")
        self.history = self.load_history()
        self.max_history = 8
        self.selected_item = None
        self.workbook = None
        self.sheet_data = {}
        self.file_type = None
        self.sheet_headers = {}  # 缓存工作表标题行
        
        self.create_widgets()
    
    def get_app_data_path(self, filename):
        """获取应用数据目录下的文件路径，确保可写"""
        # 检查是否在打包环境中运行
        if getattr(sys, 'frozen', False):
            # 打包后的路径处理
            if sys.platform == 'win32':
                # Windows系统通常使用AppData目录
                app_data_dir = os.path.join(os.environ['APPDATA'], 'ExcelFilterTool')
            else:
                # macOS/Linux系统使用用户主目录下的隐藏目录
                app_data_dir = os.path.join(os.path.expanduser('~'), '.excel_filter_tool')
            
            # 确保目录存在
            os.makedirs(app_data_dir, exist_ok=True)
            return os.path.join(app_data_dir, filename)
        else:
            # 开发环境下使用相对路径
            return os.path.join(os.path.dirname(os.path.abspath(__file__)), filename)
    
    def font_config(self):
        """配置中文字体"""
        try:
            default_font = tk.font.nametofont("TkDefaultFont")
            default_font.configure(family="SimHei", size=10)
            self.root.option_add("*Font", default_font)
        except AttributeError:
            self.root.option_add("*Font", ("SimHei", 10))
    
    def load_history(self):
        """加载历史记录"""
        try:
            if os.path.exists(self.history_file):
                with open(self.history_file, 'r', encoding='utf-8') as f:
                    history = json.load(f)
                    return history
            return []
        except Exception as e:
            messagebox.showerror("错误", f"加载历史记录失败: {str(e)}")
            # 创建一个新的空文件
            self.save_history()
            return []

    def save_history(self):
        """保存历史记录"""
        try:
            # 确保目录存在
            os.makedirs(os.path.dirname(self.history_file), exist_ok=True)
            
            with open(self.history_file, 'w', encoding='utf-8') as f:
                json.dump(self.history, f, ensure_ascii=False, indent=2)
        except Exception as e:
            messagebox.showerror("错误", f"保存历史记录失败: {str(e)}")
            
    def add_to_history(self, file_path):
        """添加文件路径到历史记录"""
        for item in self.history:
            if item['path'] == file_path:
                self.history.remove(item)
                break
        
        self.history.insert(0, {
            'path': file_path,
            'timestamp': datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            'name': os.path.basename(file_path)
        })
        
        if len(self.history) > self.max_history:
            self.history = self.history[:self.max_history]
        
        self.save_history()
        self.update_history_menu()
    
    def create_widgets(self):
        """创建界面组件"""
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.pack(fill=tk.BOTH, expand=True)
        main_frame.columnconfigure(0, weight=1)
        
        # 文件选择区域
        file_frame = ttk.Frame(main_frame)
        file_frame.grid(row=0, column=0, sticky="ew", pady=(0, 10))
        
        ttk.Button(file_frame, text="选择Excel文件", command=self.select_file).pack(side=tk.LEFT, padx=(0, 10))
        
        self.file_path_var = tk.StringVar()
        ttk.Entry(file_frame, textvariable=self.file_path_var, width=50, state="readonly").pack(side=tk.LEFT, fill=tk.X, expand=True, padx=(0, 10))
        
        history_btn = ttk.Menubutton(file_frame, text="历史记录")
        history_btn.pack(side=tk.RIGHT)
        
        self.history_menu = tk.Menu(history_btn, tearoff=0)
        history_btn.configure(menu=self.history_menu)
        self.update_history_menu()
        
        # 筛选条件区域
        filter_frame = ttk.LabelFrame(main_frame, text="筛选条件")
        filter_frame.grid(row=1, column=0, sticky="ew", pady=(0, 10))
        
        ttk.Label(filter_frame, text="条件列:").pack(side=tk.LEFT, padx=(10, 5))
        self.condition_col_var = tk.StringVar(value="I")
        ttk.Entry(filter_frame, textvariable=self.condition_col_var, width=3).pack(side=tk.LEFT, padx=(0, 10))
        
        ttk.Label(filter_frame, text="条件类型:").pack(side=tk.LEFT, padx=(0, 5))
        self.condition_type_var = tk.StringVar(value="等于")
        # condition_types = ["等于", "大于", "小于", "大于等于", "小于等于", "介于"]
        condition_types = ["等于","介于"]
        ttk.Combobox(filter_frame, textvariable=self.condition_type_var, values=condition_types, state="readonly", width=8).pack(side=tk.LEFT, padx=(0, 10))
        
        ttk.Label(filter_frame, text="数值:").pack(side=tk.LEFT, padx=(0, 5))
        self.condition_value_var = tk.StringVar()
        ttk.Entry(filter_frame, textvariable=self.condition_value_var, width=15).pack(side=tk.LEFT, padx=(0, 10))
        
        ttk.Label(filter_frame, text="查询列:").pack(side=tk.LEFT, padx=(10, 5))
        self.search_col_var = tk.StringVar(value="H")
        ttk.Entry(filter_frame, textvariable=self.search_col_var, width=3).pack(side=tk.LEFT, padx=(0, 10))
        
        ttk.Label(filter_frame, text="查询内容:").pack(side=tk.LEFT, padx=(0, 5))
        self.search_text_var = tk.StringVar()
        ttk.Entry(filter_frame, textvariable=self.search_text_var, width=15).pack(side=tk.LEFT, padx=(0, 10))
        
        ttk.Button(filter_frame, text="查询", command=self.filter_data).pack(side=tk.RIGHT, padx=(0, 10))
        
        # 结果显示区域
        result_frame = ttk.Frame(main_frame)
        result_frame.grid(row=2, column=0, sticky="nsew", pady=(0, 10))
        main_frame.rowconfigure(2, weight=1)
        
        result_frame.columnconfigure(0, weight=2)  # 左侧占2/3
        result_frame.columnconfigure(1, weight=1)  # 右侧占1/3
        result_frame.rowconfigure(0, weight=1)     # 使内容垂直填充
        
        # 左侧 - 结果列表
        left_frame = ttk.LabelFrame(result_frame, text="查询结果")
        left_frame.grid(row=0, column=0, sticky="nsew", padx=(0, 5))
        left_frame.columnconfigure(0, weight=1)
        left_frame.rowconfigure(0, weight=1)
        
        columns = ["工作表", "行号", "型号", "容量"]
        self.tree = ttk.Treeview(left_frame, columns=columns, show="headings")
        
        self.tree.heading("工作表", text="工作表", anchor=tk.CENTER)
        self.tree.heading("行号", text="行号", anchor=tk.CENTER)
        self.tree.heading("型号", text="型号", anchor=tk.W)
        self.tree.heading("容量", text="容量", anchor=tk.CENTER)
        
        self.tree.column("工作表", width=80, minwidth=60, anchor=tk.CENTER)
        self.tree.column("行号", width=60, minwidth=50, anchor=tk.CENTER)
        self.tree.column("型号", width=100, minwidth=80, anchor=tk.W, stretch=tk.YES)
        self.tree.column("容量", width=100, minwidth=80, anchor=tk.CENTER)
        
        scrollbar_y = ttk.Scrollbar(left_frame, orient=tk.VERTICAL, command=self.tree.yview)
        scrollbar_x = ttk.Scrollbar(left_frame, orient=tk.HORIZONTAL, command=self.tree.xview)
        self.tree.configure(yscroll=scrollbar_y.set, xscroll=scrollbar_x.set)
        
        self.tree.grid(row=0, column=0, sticky="nsew")
        scrollbar_y.grid(row=0, column=1, sticky="ns")
        scrollbar_x.grid(row=1, column=0, sticky="ew")
        
        # 右侧 - 详细信息
        right_frame = ttk.LabelFrame(result_frame, text="详细信息")
        right_frame.grid(row=0, column=1, sticky="nsew", padx=(5, 0))
        right_frame.columnconfigure(0, weight=1)
        right_frame.rowconfigure(0, weight=1)
        
        self.detail_canvas = tk.Canvas(right_frame, highlightthickness=0)
        self.detail_canvas.grid(row=0, column=0, sticky="nsew")
        
        self.detail_frame = ttk.Frame(self.detail_canvas)
        self.detail_window = self.detail_canvas.create_window((0, 0), window=self.detail_frame, anchor=tk.NW)
        
        detail_scrollbar = ttk.Scrollbar(right_frame, orient=tk.VERTICAL, command=self.detail_canvas.yview)
        detail_scrollbar.grid(row=0, column=1, sticky="ns")
        self.detail_canvas.configure(yscrollcommand=detail_scrollbar.set)
        
        self.detail_frame.bind("<Configure>", lambda e: self.detail_canvas.configure(scrollregion=self.detail_canvas.bbox("all")))
        self.detail_canvas.bind("<Configure>", lambda e: self.detail_canvas.itemconfig(self.detail_window, width=e.width))
        
        # 鼠标滚轮事件
        self.tree.bind("<MouseWheel>", self.on_tree_mousewheel)
        self.tree.bind("<Button-4>", self.on_tree_mousewheel)
        self.tree.bind("<Button-5>", self.on_tree_mousewheel)
        
        self.detail_canvas.bind("<MouseWheel>", self.on_detail_mousewheel)
        self.detail_canvas.bind("<Button-4>", self.on_detail_mousewheel)
        self.detail_canvas.bind("<Button-5>", self.on_detail_mousewheel)
        
        # 状态栏
        self.status_var = tk.StringVar(value="准备就绪")
        self.status_bar = ttk.Label(main_frame, textvariable=self.status_var, relief=tk.SUNKEN, anchor=tk.W)
        self.status_bar.grid(row=3, column=0, sticky="ew")
        
        self.tree.bind("<<TreeviewSelect>>", self.on_item_select)
    
    def on_tree_mousewheel(self, event):
        """处理查询结果区域的鼠标滚轮事件"""
        if event.num == 4 or event.delta > 0:
            self.tree.yview_scroll(-1, "units")
        elif event.num == 5 or event.delta < 0:
            self.tree.yview_scroll(1, "units")
        return "break"
    
    def on_detail_mousewheel(self, event):
        """处理详细信息区域的鼠标滚轮事件"""
        if event.num == 4 or event.delta > 0:
            self.detail_canvas.yview_scroll(-1, "units")
        elif event.num == 5 or event.delta < 0:
            self.detail_canvas.yview_scroll(1, "units")
        return "break"
    
    def update_history_menu(self):
        """更新历史记录菜单"""
        self.history_menu.delete(0, tk.END)
        if not self.history:
            self.history_menu.add_command(label="无历史记录", state=tk.DISABLED)
            return
        
        for item in self.history:
            self.history_menu.add_command(
                label=f"{item['name']} - {item['timestamp']}",
                command=lambda path=item['path']: self.load_history_file(path)
            )
        
        self.history_menu.add_separator()
        self.history_menu.add_command(label="清除历史记录", command=self.clear_history)
    
    def clear_history(self):
        """清除历史记录"""
        if messagebox.askyesno("确认", "确定要清除所有历史记录吗?"):
            self.history = []
            self.save_history()
            self.update_history_menu()
    
    def select_file(self):
        """选择Excel文件（自动识别xlsx/xls）"""
        file_path = filedialog.askopenfilename(
            filetypes=[("Excel files", "*.xlsx;*.xls")]
        )
        if file_path:
            try:
                self.file_path = file_path
                self.file_type = self.get_file_type(file_path)
                self.load_workbook()
                self.file_path_var.set(file_path)
                self.add_to_history(file_path)
                self.sheet_data = {}  # 清空缓存
                self.sheet_headers = {}  # 清空标题缓存
            except Exception as e:
                messagebox.showerror("错误", f"打开文件失败: {str(e)}")
    
    def get_file_type(self, path):
        """获取文件类型"""
        ext = os.path.splitext(path)[1].lower()
        if ext == '.xlsx':
            return 'xlsx'
        elif ext == '.xls':
            return 'xls'
        else:
            raise ValueError("不支持的文件格式")
    
    def load_workbook(self):
        """根据文件类型加载工作簿"""
        if self.file_type == 'xlsx':
            self.workbook = openpyxl.load_workbook(self.file_path, data_only=True)
        elif self.file_type == 'xls':
            self.workbook = xlrd.open_workbook(self.file_path)
    
    def load_history_file(self, file_path):
        """从历史记录加载文件（自动适配格式）"""
        if os.path.exists(file_path):
            try:
                self.file_path = file_path
                self.file_type = self.get_file_type(file_path)
                self.load_workbook()
                self.file_path_var.set(file_path)
                self.sheet_data = {}  # 清空缓存
                self.sheet_headers = {}  # 清空标题缓存
            except Exception as e:
                messagebox.showerror("错误", f"加载文件失败: {str(e)}")
                self.history = [item for item in self.history if item['path'] != file_path]
                self.save_history()
                self.update_history_menu()
    
    def convert_column(self, col_str):
        """将列标识（字母或数字）转换为索引（openpyxl/xlrd列索引从1开始）"""
        try:
            if col_str.isdigit():
                return int(col_str)
            else:
                return column_index_from_string(col_str.upper())
        except ValueError:
            messagebox.showerror("错误", f"无效的列标识: {col_str}")
            return None
    
    def on_item_select(self, event=None):
        """处理表格项选择事件（从缓存获取数据）"""
        for widget in self.detail_frame.winfo_children():
            widget.destroy()
        
        selected_items = self.tree.selection()
        if not selected_items:
            return
        
        self.selected_item = selected_items[0]
        values = self.tree.item(self.selected_item, "values")
        sheet_name, row_num_display = values[0], values[1]
        row_idx = int(row_num_display) - 2  # 显示行号从2开始，对应数据索引0
        
        if sheet_name not in self.sheet_data:
            messagebox.showerror("错误", "工作表数据未加载")
            return
        
        sheet_rows = self.sheet_data[sheet_name]
        if row_idx < 0 or row_idx >= len(sheet_rows):
            ttk.Label(self.detail_frame, text="行号无效", foreground="red").pack()
            return
        
        row_data = sheet_rows[row_idx]
        header_row = self.get_sheet_header(sheet_name)
        
        text_widget = tk.Text(self.detail_frame, wrap=tk.WORD, font=('SimHei', 10), bd=0, padx=5, pady=5)
        text_widget.pack(fill=tk.BOTH, expand=True)
        text_widget.config(state=tk.NORMAL)
        
        for col_idx, value in enumerate(row_data):
            if col_idx < len(header_row):
                col_name = header_row[col_idx] or f"列{col_idx+1}"
            else:
                col_name = f"列{col_idx+1}"
            
            text_widget.insert(tk.END, f"{col_name}: {value}\n")
        
        text_widget.config(state=tk.DISABLED)
        
        copy_btn = ttk.Button(self.detail_frame, text="复制数据", command=lambda: self.copy_to_clipboard(text_widget))
        copy_btn.pack(pady=5)
    
    def copy_to_clipboard(self, text_widget):
        """复制文本内容到剪贴板"""
        try:
            text = text_widget.get("1.0", tk.END)
            self.root.clipboard_clear()
            self.root.clipboard_append(text)
            messagebox.showinfo("成功", "数据已复制到剪贴板")
        except Exception as e:
            messagebox.showerror("错误", f"复制失败: {str(e)}")
    
    def parse_range_value(self, value_str):
        """解析范围值，支持-或,作为分隔符"""
        value_str = value_str.replace('，', ',').replace('－', '-')
        
        if '-' in value_str:
            parts = value_str.split('-', 1)
        elif ',' in value_str:
            parts = value_str.split(',', 1)
        else:
            return None, None
        
        try:
            min_val = float(parts[0].strip())
            max_val = float(parts[1].strip())
            return min_val, max_val
        except (ValueError, IndexError):
            return None, None
    
    def filter_data(self):
        """筛选数据（兼容xlsx和xls）"""
        self.tree.delete(*self.tree.get_children())
        if not self.file_path or not self.workbook:
            messagebox.showerror("错误", "请先选择有效的Excel文件")
            return
        
        condition_col = self.condition_col_var.get().strip()
        condition_type = self.condition_type_var.get()
        condition_value_str = self.condition_value_var.get().strip()
        search_col = self.search_col_var.get().strip()
        search_text = self.search_text_var.get().strip().lower()
        
        cond_col_idx = self.convert_column(condition_col)
        search_col_idx = self.convert_column(search_col)
        if cond_col_idx is None or search_col_idx is None:
            return
        
        min_val, max_val = None, None
        if condition_type == "介于":
            min_val, max_val = self.parse_range_value(condition_value_str)
            if min_val is None:
                return
        
        result_count = 0
        self.sheet_data = {}
        self.sheet_headers = {}  # 重置标题行缓存
        
        # 遍历工作表
        if self.file_type == 'xlsx':
            sheet_names = self.workbook.sheetnames[::-1]
        else:  # xls
            sheet_names = [self.workbook.sheet_names()[i] for i in reversed(range(len(self.workbook.sheet_names())))]
        
        for sheet_name in sheet_names:
            try:
                # 先获取标题行
                header = self.get_sheet_header(sheet_name)
                
                # 再获取数据行
                sheet_data = self.read_sheet_data(sheet_name)
                self.sheet_data[sheet_name] = sheet_data
                
                for row_idx, row in enumerate(sheet_data, 2):  # 显示行号从2开始
                    if len(row) < max(cond_col_idx, search_col_idx):
                        continue
                    
                    cond_value = row[cond_col_idx - 1]
                    search_value = str(row[search_col_idx - 1]).lower() if row[search_col_idx - 1] is not None else ""
                    
                    cond_met = self.check_condition(cond_value, condition_type, min_val, max_val, condition_value_str)
                    search_met = search_text in search_value if search_text else True
                    
                    if cond_met and search_met:
                        self.tree.insert("", tk.END, values=[
                            sheet_name,
                            row_idx,
                            row[search_col_idx - 1] or "",
                            cond_value or ""
                        ])
                        result_count += 1
            except Exception as e:
                continue
        
        self.status_var.set(f"找到 {result_count} 条记录")
        if result_count > 0:
            first_item = self.tree.get_children()[0]
            self.tree.selection_set(first_item)
            self.on_item_select()
    
    def read_sheet_data(self, sheet_name):
        """根据文件类型读取工作表数据"""
        if self.file_type == 'xlsx':
            sheet = self.workbook[sheet_name]
            rows = sheet.iter_rows(min_row=2, values_only=True)  # 从第二行开始读取数据
            return [list(row) for row in rows]
        else:  # xls
            sheet = self.workbook.sheet_by_name(sheet_name)
            return [sheet.row_values(rowx, start_colx=0) for rowx in range(1, sheet.nrows)]  # 跳过标题行
    
    def get_sheet_header(self, sheet_name):
        """获取工作表的标题行（第一行）"""
        if sheet_name in self.sheet_headers:
            return self.sheet_headers[sheet_name]
        
        try:
            if self.file_type == 'xlsx':
                sheet = self.workbook[sheet_name]
                header = [cell.value for cell in sheet[1]]  # 第一行是标题（索引1）
            else:  # xls
                sheet = self.workbook.sheet_by_name(sheet_name)
                header = sheet.row_values(0)  # 第一行是标题（索引0）
            
            self.sheet_headers[sheet_name] = header
            return header
        except Exception as e:
            return []  # 出错时返回空列表
    
    def check_condition(self, cond_value, condition_type, min_val, max_val, condition_value_str):
        """统一处理条件判断"""
        if condition_type == "介于":
            if isinstance(cond_value, (int, float)):
                return min_val <= cond_value <= max_val
            return False
        else:
            if not condition_value_str:
                return True  # 无条件值时默认满足
            
            try:
                target = float(condition_value_str)
                if isinstance(cond_value, (int, float)):
                    if condition_type == "等于":
                        return cond_value == target
                    elif condition_type == "大于":
                        return cond_value > target
                    elif condition_type == "小于":
                        return cond_value < target
                    elif condition_type == "大于等于":
                        return cond_value >= target
                    elif condition_type == "小于等于":
                        return cond_value <= target
            except (ValueError, TypeError):
                return False
            return False

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelFilterTool(root)
    root.mainloop()